<?php
    define('CONFIG_FILE',true);
    include '../config.php';
    
    define('DB_FILE',true);
    require_once '../Tool/DB/SqlDBManager.class.php';
    require_once '../Tool/Common/PrepareInput.php';

    session_start();
    if (empty($_SESSION["userType"])) {
        $url="../Index.php";
		include_once '../Tool/Common/Redirect.php';
		exit();
    }

    if(!empty($_SESSION['sortBy'])){
        $sort=$_SESSION['sortBy'];
    }else{
        $sort="client.NRIC";
    }
    $order='asc';

    $startDate=$endDate=$centreId=$ActivityId="";  
    
    if(isset($_SESSION['startDate'])){ 
        $startDate=prepareInput($_SESSION['startDate']); 
        $startDate = date('Y-m-d', strtotime($_SESSION['startDate']));
    }
    if(isset($_SESSION['endDate'])) { 
        $endDate=prepareInput($_SESSION['endDate']);
        $endDate = date('Y-m-d', strtotime($_SESSION['endDate'])); 
    }
    if(isset($_SESSION['centreId'])){ 
        $centreId=prepareInput($_SESSION['centreId']); 
    }
    if(isset($_SESSION['activityId'])){ 
        $ActivityId=prepareInput($_SESSION['activityId']); 
    }

    //Validate input
    if(!preg_match("/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/",$startDate)){
        echo "<font color=red>Invalid Start Date Format.</font><br/>";
        exit();
    }
    if(!preg_match("/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/",$endDate)){
        echo "<font color=red>Invalid End Date Format.</font><br/>";
        exit();
    }
    if(!preg_match("/^[0-9]*$/",$centreId)){
        echo "<font color=red>Invalid Centre ID.</font><br/>";
        exit();
    }
    if(!preg_match("/^[0-9]*$/",$ActivityId)){
        echo "<font color=red>Invalid Activity ID.</font><br/>";
        exit();
    }

    include 'conn.php';
    
    $actsql = mysql_query("Select ActivityName from activity where ActivityId='$ActivityId'");

    while ($row = mysql_fetch_array($actsql)) {
        $ActivityName = $row['ActivityName'];
    }
    $startDate = $_SESSION["startDate"];
    $endDate = $_SESSION["endDate"];

    //date range
    $range = (strtotime($endDate)- strtotime($startDate))/24/3600;

    if ($centreId == 0) {
        $clwhere = "client.CentreId is not null";
    //echo "testing ";
        $where = "CentreId is not null";
        $kwhere = "INNER JOIN Centre on client.CentreId=centre.CentreId where client.CentreId is not null order by $sort $order";
    } else {
        $clwhere = "client.CentreId=$centreId";
        $where = "CentreId=$centreId";
        $kwhere = "INNER JOIN Centre on client.CentreId=centre.CentreId where client.CentreId=$centreId order by $sort $order";
    //echo "testing2";
    }


    require_once '../Classes/PHPExcel.php';
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->setActiveSheetIndex(0);
    $startDate1 = date('dmy', strtotime($startDate));
    $endDate1 = date('dmy', strtotime($endDate));
    //$objConditional1 = new PHPExcel_Style_Conditional();
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
    $objPHPExcel->getActiveSheet()->getStyle('A3:Z3')->getFont()->setBold(true);
    $objPHPExcel->getProperties()->setTitle("EldercareActivityReport" . $startDate . "to" . $endDate);
    if ($centreId == 0) {
        $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Eldercare Activity Report from ' . $startDate1 . ' to ' . $endDate1);
    } else {
        include 'conn.php';
        $check1 = "Select CentreName from Centre where CentreId=$centreId";
        $resultto = mysql_query($check1);
        while ($row = mysql_fetch_array($resultto)) {
            $cname = $row['CentreName'];
        }
        $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Eldercare Activity Report from ' . $startDate1 . ' to ' . $endDate1 . ' for ' . $cname);
    }

    $objPHPExcel->getActiveSheet()->mergeCells('A1:N1');
    $objPHPExcel->getActiveSheet()->getStyle('A1:N1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


    include 'conn.php';
    $checkDateSNo = $startDate;
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(9);
    $objPHPExcel->getActiveSheet()->mergeCells('C3:D3');
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
    $objPHPExcel->getActiveSheet()->SetCellValue('A3', 'S/No');
    $objPHPExcel->getActiveSheet()->SetCellValue('B3', 'NRIC');
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(13);
    $objPHPExcel->getActiveSheet()->SetCellValue('C3', 'Name');
    $column = 'E';
    $userType = $_SESSION["userType"];
    if ($userType == 'System Admin') {
        $objPHPExcel->getActiveSheet()->SetCellValue($column . '3', 'Centre Name');

        $objPHPExcel->getActiveSheet()->getColumnDimension($column)->setWidth(25);
        $column++;
    }
    $objPHPExcel->getActiveSheet()->SetCellValue($column . '3', 'Daycare');
    $objPHPExcel->getActiveSheet()->getStyle($column . '3')->getAlignment()->setWrapText(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension($column)->setWidth(13);
    $column++;
    $objPHPExcel->getActiveSheet()->SetCellValue($column . '3', 'Age');
    $objPHPExcel->getActiveSheet()->getStyle($column . '3')->getAlignment()->setWrapText(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension($column)->setWidth(13);
    $column++;

    $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(13);
    $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(13);

    //Date columns
    $sqlDBManager = new SqlDBManager();
    
    $sql="select date from holiday";
    $res=$sqlDBManager->queryRows($sql);
    
    $Holiday=array();

    if(!empty($res)){
        for($i=0;$i<count($res);$i++)
        {
            array_push($Holiday, $res[$i]);
        }
    }

    $rowCount = 3;
    for($i=0;$i<=$range;$i++){
        $date=Date('Y-m-d', strtotime($startDate."+".$i." days"));
        for($j=0;$j<count($Holiday);$j++){
            if($Holiday[$j][0]==Date('Y-m-d', strtotime($startDate."+".$i." days")) || date('l', strtotime(Date('Y-m-d', strtotime($startDate."+".$i." days"))))== "Sunday" || date('l', strtotime(Date('Y-m-d', strtotime($startDate."+".$i." days"))))== "Saturday"){
                $i++;
            }       
        }
        $realDate=Date('d/m/Y', strtotime($startDate."+".$i." days"));
        $objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, $realDate);
        $column++;
    }
    
    /*$count = 1;
    while ($checkDateSNo <= $endDate) {
        $sqlhols = "Select count(*) from holiday where Date='$checkDateSNo'";
        $resulthols = mysql_query($sqlhols);

        while ($row = mysql_fetch_array($resulthols)) {
            if ($row['count(*)'] == 0) {
                $day = date('l', strtotime($checkDateSNo));
                if ($day != "Sunday" && $day != "Saturday") {
                    $objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, $count.' Day');
                    $objPHPExcel->getActiveSheet()->getStyle($column . $rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $checkDateSNo = date('Y-m-d', strtotime('+1 day', strtotime($checkDateSNo)));
                    $count++;
                    $column++;
                } else {
                    $checkDateSNo = date('Y-m-d', strtotime('+1 day', strtotime($checkDateSNo)));
                }
            } else {
                $checkDateSNo = date('Y-m-d', strtotime('+1 day', strtotime($checkDateSNo)));
            }
        }
    }*/
    $objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, 'Average Performance');
    $objPHPExcel->getActiveSheet()->getColumnDimension($column)->setWidth(13);
    $objPHPExcel->getActiveSheet()->getStyle($column . $rowCount)->getAlignment()->setWrapText(true);
    $objPHPExcel->getActiveSheet()->getStyle($column . $rowCount)->getFont()->setBold(true);
    $column++;
    $objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, 'Total Attendance');
    $objPHPExcel->getActiveSheet()->getColumnDimension($column)->setWidth(13);
    $objPHPExcel->getActiveSheet()->getStyle($column . $rowCount)->getAlignment()->setWrapText(true);
    $objPHPExcel->getActiveSheet()->getStyle($column . $rowCount)->getFont()->setBold(true);
    $rowCount++;

    $ttlattendance = 0;
    include 'conn.php';
    $sql = "SELECT * from Client $kwhere";
    $result = mysql_query($sql);
    $sno = 1;
    while ($row = mysql_fetch_array($result)) {
        $objPHPExcel->getActiveSheet()->mergeCells('C' . $rowCount . ':D' . $rowCount);
        $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $sno);
        $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $row['NRIC']);
        $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $row['CustomerName']);
        $column = 'E';
        if ($userType == 'System Admin') {
            $objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, $row['CentreName']);
            //$objPHPExcel->getActiveSheet()->getStyle($column . $rowCount)->getAlignment()->setWrapText(true);
            $column++;
        }
        $objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, $row['Daycare']);
        $column++;
        $Age = date('Y-m-d') - $row['DateOfBirth'];
        $objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, $Age);
        $column++;

        $checkDate = $startDate;
        $workingday = 0;
        $attendance = 0;
        $totalPerformance = 0;
        while ($checkDate <= $endDate) {
            $sqlhols1 = "Select count(*) from holiday where Date='$checkDate'";
            $resulthols1 = mysql_query($sqlhols1);

            while ($rowhol = mysql_fetch_array($resulthols1)) {
                if ($rowhol['count(*)'] == 0) {
                    $day = date('l', strtotime($checkDate));
                    if ($day != "Sunday" && $day != "Saturday") {
                        $sqlattendance = "Select * from activityrecord where date(Date)='$checkDate' and ActivityId='$ActivityId' and NRIC='" . $row['NRIC'] . "' and $where";
                        $checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
                        $workingday++;
                        $resultattendance = mysql_query($sqlattendance);
                        if (mysql_num_rows($resultattendance) == 1) {
                            $row1 = mysql_fetch_array($resultattendance);
                            $performance = $row1['Performance'];
                            $objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, $performance);
                            $attendance++;
                            $totalPerformance += $performance;
                        } else {
                            $objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, ' - ');
                        }
                        $column++;
                    } else {
                        $checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
                    }
                } else {
                    $checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
                }
            }
        }
        $ttlattendance += $attendance;
        if ($attendance == 0) {
            $averagePerformance = 0;
        } else {
            $averagePerformance = round($totalPerformance / $attendance, 5);
        }
        $avg = round($ttlattendance / $sno, 5);
        $objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, $averagePerformance);
        $column++;
        $objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, $attendance);
    //$str.="," . $attendance . "\n";
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                )
            )
        );
        $style2Array = array(
            'borders' => array(
                'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THICK, PHPExcel_Style_Border::BORDER_DOUBLE
                )
            )
        );
        $objPHPExcel->getActiveSheet()->getStyle('A3:' . $column . $rowCount)->applyFromArray($styleArray);
        $sno++;
        $rowCount++;
    }
    $objPHPExcel->getActiveSheet()->getStyle('F4:' . $column . $rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowCount, 'Total Attendance for ' . $ActivityName . ':');
    $objPHPExcel->getActiveSheet()->mergeCells('A' . $rowCount . ':C' . $rowCount);
    $objPHPExcel->getActiveSheet()->getStyle('A' . $rowCount . ':D' . $rowCount)->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->setCellValue('D' . $rowCount, $ttlattendance);
    $rowCount++;
    $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowCount, 'Average Attendance for ' . $ActivityName . ':');
    $objPHPExcel->getActiveSheet()->mergeCells('A' . $rowCount . ':C' . $rowCount);
    $objPHPExcel->getActiveSheet()->getStyle('A' . $rowCount . ':D' . $rowCount)->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->setCellValue('D' . $rowCount, $avg);
    //creating the excel file
    //MAILING
    //header('Content-Disposition: attachment;filename="EldercareReport' . $startDate1 . 'to' . $endDate1 . '.xlsx"');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    header('Content-Type: application/vnd.ms-excel');
    if ($centreId == 0) {
        header('Content-Disposition: attachment;filename="EldercareActivityReport' . $startDate1 . 'to' . $endDate1 . '.xlsx"');
    } else {
        include 'conn.php';
        $check1 = "Select CentreName from Centre where CentreId=$centreId";
        $resultto = mysql_query($check1);
        while ($row = mysql_fetch_array($resultto)) {
            $cname = $row['CentreName'];
        }
        header('Content-Disposition: attachment;filename="EldercareActivityReport' . $startDate1 . 'to' . $endDate1 . '-' . $cname . '.xlsx"');
    }


    header('Cache-Control: max-age=0');
    $objWriter->save('php://output');
    exit();
    //$objWriter->save(str_replace('export_1.php', 'EldercareReport' . $startDate1 . 'to' . $endDate1 . '.xlsx', __FILE__));
    //$objWriter->save('php://output');
?>

